﻿/**************************************************************
*作者：Leon
*创建时间：2022/4/30 16:18:38
**************************************************************/
using CourseManagerment.Model;
using CourseManagerment.ViewModel;
using LiveCharts;
using LiveCharts.Defaults;
using LiveCharts.Wpf;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace CourseManagerment
{
    /// <summary>
    /// 备注：这里为了方便引入三层架构，后期可以进行改进，将数据读取剥离
    /// </summary>
    class LocalDataAccess
    {
        private static LocalDataAccess? instance;
        private LocalDataAccess() { }
        public static LocalDataAccess GetInstance()
        {
            return instance ??= new LocalDataAccess();
        }
        SqlCommand? comm;
        SqlConnection? conn;
        SqlDataAdapter? adapter;

        /// <summary>
        /// 打开数据库
        /// </summary>
        /// <returns></returns>
        private bool DBConnection()
        {
            string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
            if (conn==null)
            {
                conn = new SqlConnection(connStr);
            }
            try
            {
                conn.Open();
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 用户信息验证
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public User CheckUserInfo(string userName, string pwd)
        {
            try
            {
                if (DBConnection())
                {
                    string userSql = "select * from Users where UserName=@UserName and Password=@pwd and IsValidation=1";
                    adapter = new SqlDataAdapter(userSql, conn);
                    adapter.SelectCommand.Parameters.Add(new SqlParameter("@UserName", System.Data.SqlDbType.VarChar) { Value = userName });
                    adapter.SelectCommand.Parameters.Add(new SqlParameter("@pwd", System.Data.SqlDbType.VarChar)
                    {
                        Value =
                        MD5Provider.GetMD5String(pwd+"@"+userName)
                    });

                    DataTable table = new DataTable();
                    int count = adapter.Fill(table);

                    if (count < 1)
                        throw new Exception("用户名或密码不正确!");

                    DataRow dr = table.Rows[0];
                    if (dr.Field<int>("CanLogin") == 0)
                        throw new Exception("当前用户没有权限使用此平台！");

                    User userInfo = new()
                    {
                        UserName = dr.Field<string>("UserName"),
                        RealName = dr.Field<string>("RealName"),
                        Password = dr.Field<string>("Password"),
                        Avatar = dr.Field<string>("Avatar"),
                        Gender = dr.Field<int>("Gender")
                    };
                    return userInfo;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                this.Dispose();
            }
            return null;
        }
        /// <summary>
        /// 获取数据库中的老师
        /// </summary>
        /// <returns></returns>
        public List<string?> GetTeachers()
        {
            List<string?> result = new();
            try
            {
                if (this.DBConnection())
                {
                    string sql = "select RealName from Users where IsTeacher=1";
                    adapter = new SqlDataAdapter(sql, conn);

                    DataTable table = new();
                    int count = adapter.Fill(table);
                    if (count>0)
                    {
                        result = table.AsEnumerable().Select(c => c.Field<string>("RealName")).ToList();
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                this.Dispose();
            }
            return result;
        }

        internal List<CourseModel> GetCourses()
        {
            try
            {
                List<CourseModel> result = new();
                if (this.DBConnection())
                {
                    string sql = @"select a.CourseId,a.CourseName,a.CourseCover,a.CourseHttp,a.Description,c.RealName
from Courses a
left join CourseTeacherRelation b
on b.CourseId=a.CourseId
left join Users c
on b.TeacherId=c.UserId
order by a.CourseId,b.TeacherId";

                    adapter = new SqlDataAdapter(sql, conn);


                    DataTable table = new DataTable();
                    int count = adapter.Fill(table);
                    if (count > 0)
                    {
                        string? courseid = "";
                        CourseModel? model = null;
                        foreach (DataRow dr in table.AsEnumerable())
                        {
                            string? tempId = dr.Field<string>("CourseId");
                            if (courseid!=tempId)
                            {
                                courseid = tempId;
                                model = new ()
                                {
                                    CourseName = dr.Field<string>("CourseName"),
                                    Cover  = dr.Field<string>("CourseCover "),
                                    Url = dr.Field<string>("CourseHttp"),
                                    Description = dr.Field<string>("dwserigtion"),
                                    Teachers=new List<string>()
                                };
                                result.Add(model);
                            }
                            if (model!=null)
                            {
                                model.Teachers.Add(dr.Field<string>("RealName"));
                            }
                        }
                    }
                }
                return result;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                this.Dispose();
            }
        }
        /// <summary>
        /// 获取课程点击信息
        /// </summary>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        internal List<CourseSeriesModel> GetCoursePlayRecord()
        {
            try
            {
                List<CourseSeriesModel> cModelList = new List<CourseSeriesModel>();
                if (DBConnection())
                {
                    string userSql = @"select a.CourseName,a.CourseId,b.PlayCount,b.IsGrowing,b.GrowingRato, 
c.PlatfromName
from Courses a
left join PlayRecord b
on a.CourseId=b.CourseId
left join Platforms c
on b.PlatformId=c.PlatfromId
order by a.CourseId,c.PlatfromId";
                    adapter = new SqlDataAdapter(userSql, conn);


                    DataTable table = new DataTable();
                    int count = adapter.Fill(table);

                    string courseid = "";
                    CourseSeriesModel cModel = null;

                    foreach (DataRow dr in table.AsEnumerable())
                    {
                        string tempId = dr.Field<string>("CourseId");
                        if (courseid!= tempId)
                        {
                            courseid = tempId;
                            cModel = new CourseSeriesModel();

                            cModelList.Add(cModel);
                            cModel.CourseName= dr.Field<string>("CourseName");
                            cModel.SeriesColection = new LiveCharts.SeriesCollection();
                            cModel.SeriesList = new System.Collections.ObjectModel.ObservableCollection<SeriesModel>();
                        }
                        if (cModel!=null)
                        {
                            cModel.SeriesColection.Add(new PieSeries
                            {
                                Title = dr.Field<string>("PlatfromName"),
                                Values= new ChartValues<ObservableValue> {
                                    new ObservableValue((double)dr.Field<decimal>("PlayCount")) },
                                DataLabels = false
                            });

                            cModel.SeriesList.Add(new SeriesModel
                            {
                                SeriesName = dr.Field<string>("PlatfromName"),
                                CurrentValue = dr.Field<decimal>("PlayCount"),
                                IsGrowing=dr.Field<Int32>("IsGrowing")==1,
                                ChangeRate=(int)dr.Field<decimal>("GrowingRato")
                            });
                        }
                    }

                }
                return cModelList;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Dispose();
            }
        }


        public void Dispose()
        {
            if (adapter!=null)
            {
                adapter.Dispose();
                adapter = null;
            }
            if (comm!=null)
            {
                comm.Dispose();
                comm = null;
            }
            if (conn!=null)
            {
                conn.Close();
                conn.Dispose();
                conn = null;
            }
        }
    }

    public class MD5Provider
    {
        public static string GetMD5String(string str)
        {
            var md = System.Security.Cryptography.MD5.Create();
            byte[] pws = md.ComputeHash(System.Text.Encoding.UTF8.GetBytes(str));
            string pwd = "";
            foreach (var item in pws)
            {
                pwd += item.ToString("X2");
            }
            return pwd;
        }
    }
}
